How to Add WITH (NOLOCK) and Handle Parameter Sniffing in Entity Framework

WARNING

The implementation method in this article may be considered an Anti-Pattern in some modern scenarios. For more recommended alternatives (such as the RCSI architecture and TagWith implementation), please refer to the latest comprehensive discussion: Introduction to RCSI and Improved Entity Framework Locking Hint Interceptor

A few days ago during an interview, I was asked about WITH (NOLOCK) in SQL Server. Because I hadn't used it in a long time, I couldn't recall it immediately, which led to an incorrect answer. Ironically, I had relevant notes from a year ago: "SQL Server Performance Tuning".

Why is WITH (NOLOCK) important in SQL Server, yet I haven't used it in a long time? The main reason is that most development now directly uses Entity Framework, rather than manually crafting SQL statements as we did in the past. We can simply let the library modify the SQL before executing the command.

Now, let's look at how to achieve the same behavior in Entity Framework.

TIP

The complete executable sample for this article: CloudyWing/EfCoreSqlHintInterceptorSample.

Interceptor

The Interceptor in Microsoft.EntityFrameworkCore was introduced in version 3.0, while it was added to the .NET Framework's EntityFramework in version 6.0. Its primary function is to allow modification or interception of ongoing operations when Entity Framework performs low-level database operations or SaveChanges(). For more specific details, please refer to the MSDN documentation on "Interception". This article uses the Microsoft.EntityFrameworkCore version as an example.

Interceptor Interfaces

• IDbCommandInterceptor: Handles methods related to DbCommand. This article uses this interface.
• IDbConnectionInterceptor: Handles methods related to opening and closing connections.
• IDbTransactionInterceptor: Handles methods related to transactions.
• ISaveChangesInterceptor: Handles methods related to SaveChanges().

Implementation Method

Regarding the handling of WITH (NOLOCK), most solutions found online cannot handle subqueries. However, there is an article "Adding With NoLock to EF Core Queries" that takes it a step further, so I referenced it for my modifications.

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(
       DbCommand command, CommandEventData eventData,
       InterceptionResult<object> result
    ) {
        FixCommand(command);
        return base.ScalarExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<object> result, CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // In some modification scenarios, such as querying before modifying, EF might call ExecuteReader instead of ExecuteNonQuery
        // So we need to exclude these cases
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // If Single or First is called, it might be for precise data retrieval (e.g., fetching data to modify), so NOLOCK should not be added
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // Although EF-generated Select statements do not end with a semicolon, modification statements do. 
        // To be safe, we still need to handle it.
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

Code Explanation

1. DbCommandInterceptor has already implemented all methods of IDbCommandInterceptor, so you only need to inherit from it and override the required methods.
2. Methods related to querying include ExecuteReader() and ExecuteScalar(), so we override the corresponding pre-execution methods of IDbCommandInterceptor for both synchronous and asynchronous versions of these two methods.
3. CommandText modification:
   • Some modification statements that return values might use ExecuteScalar(), so we skip INSERT, UPDATE, and DELETE statements.
   • WITH (NOLOCK) is intended to avoid blocking when data is locked, but it is inappropriate if the data is being retrieved for modification. Therefore, we skip statements containing TOP(1) (e.g., First() or Find()) and TOP(2) (e.g., Single()).
   • Added OPTION (OPTIMIZE FOR UNKNOWN); to handle Parameter Sniffing.

WARNING

The above handling lacks verification in actual production environments; please adjust it according to your specific situation.

Adding the Interceptor

You can add the Interceptor using the following two methods:

• Add the following code in your DbContext:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.AddInterceptors(new FixDbCommandInterceptor());

• When injecting DbContext in DI, configure it from DbContextOptionsBuilder:

services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

Actual Execution Results

Use the following SQL to create the tables and use reverse engineering to create the EF models:

CREATE TABLE [dbo].[Test](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestInt] [int] NOT NULL,
    [TestBit] [bit] NOT NULL,
    [TestDateTime] [datetime2](7) NOT NULL,
    [TestGuid] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED (
 [Id] ASC
) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SubTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestId] [int] NOT NULL,
    CONSTRAINT [PK_SubTest] PRIMARY KEY CLUSTERED (
 [Id] ASC
) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SubTest]  WITH CHECK ADD  CONSTRAINT [FK_SubTest_Test] FOREIGN KEY([TestId])
REFERENCES [dbo].[Test] ([Id])
GO

Execute the following code:

context.Tests.Find(1);

context.Tests
    .Include(x => x.SubTests)
    .SingleOrDefault(x => x.Id == 1);

context.Tests
    .Include(x => x.SubTests)
    .ToList();

The generated SQL statements are as follows:

-- Find()
SELECT TOP(1) [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt]
FROM [Test] AS [t]
WHERE [t].[Id] = @__p_0 OPTION (OPTIMIZE FOR UNKNOWN);

-- SingleOrDefault()
SELECT [t0].[Id], [t0].[TestBit], [t0].[TestDateTime], [t0].[TestGuid], [t0].[TestInt], [s].[Id], [s].[TestId]
FROM (
    SELECT TOP(2) [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt]
    FROM [Test] AS [t]
    WHERE [t].[Id] = 1
) AS [t0]
LEFT JOIN [SubTest] AS [s] ON [t0].[Id] = [s].[TestId]
ORDER BY [t0].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

-- ToList()
SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

Change Log

• 2026-05-29 Added link to the corresponding GitHub sample project.
• 2024-07-18 Initial version created.